package com.bp.hearthealth.pulse.lnapps.bp_db.bp_bloodpressure

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Transaction
import androidx.room.Update
import com.bp.hearthealth.pulse.lnapps.bp_ui.bp_fragment.bp_tracker.bp_bean.BpBloodPressureAverageValues
import com.bp.hearthealth.pulse.lnapps.bp_ui.bp_fragment.bp_tracker.bp_bean.BpBloodPressureWithDetails

@Dao
interface BpBloodPressureDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertBloodPressure(bloodPressure: BloodPressure): Long


    @Query("SELECT * FROM blood_pressure_surface WHERE userId = :userId AND date = :date")
    suspend fun getBloodPressureByDate(userId: String, date: Long): List<BloodPressure>

    /**
     * 倒叙
     */
    @Query("SELECT * FROM blood_pressure_surface WHERE userId = :userId ORDER BY date DESC")
    suspend fun getBloodPressureByUserId(userId: String): List<BloodPressure>


    /**
     * 查询指定 userId 和时间范围内的血压数据列表
     */
    @Query("SELECT * FROM blood_pressure_data_surface WHERE id = :id AND timePeriod >= :startTime AND timePeriod <= :endTime")
    suspend fun getBloodPressureDataByUserIdAndTimeRange(
        id: String, startTime: Long, endTime: Long
    ): List<BloodPressureData>

    /**
     * 查询指定 userId 和 date 的 BloodPressure 对象
     */
    @Query("SELECT * FROM blood_pressure_surface WHERE userId = :userId AND date = :date")
    suspend fun getBloodPressureByUserIdAndDate(userId: String, date: Long): BloodPressure?

    /**
     * 指定userId的全部数据List<BloodPressureData>
     */
    @Query("SELECT * FROM blood_pressure_data_surface WHERE userId = :userId")
    fun getBloodPressureDataByUserId(userId: String): List<BloodPressureData>

    @Insert
    suspend fun insertBloodPressureData(bloodPressureDataList: List<BloodPressureData>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertBloodPressureData(bloodPressureData: BloodPressureData)

    @Query("SELECT id FROM blood_pressure_data_surface ORDER BY id DESC LIMIT 1")
    suspend fun getNewestBloodPressureDataId(): String?

    /**
     * 通过id查询BloodPressureData
     */
    @Query("SELECT * FROM blood_pressure_data_surface WHERE id = :id")
    suspend fun getBloodPressureDataById(id: String): BloodPressureData?

    @Transaction
    suspend fun insertBloodPressureWithPressureData(bloodPressure: BloodPressure): BloodPressure? {
        return try {
//            val reversedBloodPressureList = bloodPressure.bloodPressureList.reversed()
            bloodPressure.bloodPressureList.forEach { bloodPressureData ->
                insertBloodPressureData(bloodPressureData)
            }
//            insertBloodPressure(bloodPressure.copy(bloodPressureList = reversedBloodPressureList))
            insertBloodPressure(bloodPressure)
            // 获取插入后的第一条血压数据
            val insertedBloodPressure =
                getBloodPressureByDate(bloodPressure.userId, bloodPressure.date).firstOrNull()
            insertedBloodPressure
        } catch (e: Exception) {
            null
        }
    }


    /**
     * 更新指定 id 下的 BloodPressureData
     */
    @Update
    suspend fun updateBloodPressureDataUnlockedStatus(bloodPressureData: BloodPressureData)

    /**
     * 更新指定 id 下的 BloodPressureData 的 unlocked 状态
     */
    @Query("UPDATE blood_pressure_data_surface SET unlocked = :unlocked WHERE id = :id")
    suspend fun updateBloodPressureDataUnlockedStatusById(id: String, unlocked: Boolean): Int

    /**
     * 更新指定 BloodPressure 对象
     */
    @Update
    suspend fun updateBloodPressure(bloodPressure: BloodPressure)

    /**
     * 删除指定的 BloodPressureData 对象
     */
    @Delete
    suspend fun deleteBloodPressureData(bloodPressureData: BloodPressureData): Int

    @Transaction
    @Query("DELETE FROM blood_pressure_data_surface WHERE id = :bloodPressureDataId AND userId = :userId")
    suspend fun deleteBloodPressureDataById(userId: String, bloodPressureDataId: String): Int

    /**
     * 通过userId和date查询对象
     */
    @Query("SELECT * FROM blood_pressure_surface WHERE userId = :userId AND date = :date")
    suspend fun getBloodPressureByIdAndDate(userId: String, date: Long): BloodPressure?

    /**
     * 删除/清空 BloodPressure
     */
    @Delete
    suspend fun deleteBloodPressure(bloodPressure: BloodPressure)

    /**
     * 获取今天的血压数据列表
     */
    @Query("SELECT * FROM blood_pressure_data_surface WHERE userId = :userId AND timePeriod >= :startOfDay AND timePeriod <= :endOfDay")
    suspend fun getTodayBloodPressureData(
        userId: String, startOfDay: Long, endOfDay: Long
    ): List<BloodPressureData>

    /**
     * 查询指定时间范围内的血压数据列表
     */
    @Query("SELECT * FROM blood_pressure_data_surface WHERE userId = :userId AND timePeriod BETWEEN :startDate AND :endDate ORDER BY timePeriod DESC")
    suspend fun getBloodPressureDataForDate(
        userId: String, startDate: Long, endDate: Long
    ): List<BloodPressureData>

    /**
     * 降序
     */
    @Query("SELECT * FROM blood_pressure_surface WHERE userId = :userId AND timeStamp BETWEEN :startDate AND :endDate")
    suspend fun getBloodPressureForDateRange(
        userId: String, startDate: Long, endDate: Long
    ): List<BloodPressure>


    /**
     * 获取距离今天最近的一天的血压数据列表
     */
    @Query("SELECT * FROM blood_pressure_data_surface WHERE userId = :userId AND timePeriod < :today ORDER BY timePeriod DESC LIMIT 1")
    suspend fun getNearestBloodPressureData(userId: String, today: Long): List<BloodPressureData>

    /**
     * 查询指定用户 ID 下的所有 systolic、diastolic 和 pulse 三个字段的最新一条数据
     */
    @Query("SELECT systolic AS avgSystolic, diastolic AS avgDiastolic, pulse AS avgPulse FROM blood_pressure_data_surface WHERE userId = :userId ORDER BY timePeriod DESC LIMIT 1")
    fun getLatestBloodPressureData(userId: String): BpBloodPressureAverageValues

    /**
     * 查询指定用户 ID 下的所有血压数据中距离当前时间 24 小时内 systolic、diastolic 和 pulse 三个字段的平均值
     */
    @Query(
        "SELECT AVG(systolic) AS avgSystolic, AVG(diastolic) AS avgDiastolic, AVG(pulse) AS avgPulse " + "FROM blood_pressure_data_surface " + "WHERE userId = :userId AND timePeriod >= :startTime"
    )
    fun getAverageValuesForLast24Hours(
        userId: String, startTime: Long
    ): BpBloodPressureAverageValues

    /**
     * 查询指定用户 ID 下的所有 systolic、diastolic 和 pulse 三个字段的平均值
     */
    @Query(
        "SELECT AVG(systolic) AS avgSystolic, AVG(diastolic) AS avgDiastolic, AVG(pulse) AS avgPulse " + "FROM blood_pressure_data_surface " + "WHERE userId = :userId"
    )
    fun getAverageValuesForUser(userId: String): BpBloodPressureAverageValues

    /**
     * 查询指定用户 ID 下的所有 systolic、diastolic 和 pulse 三个字段的最大值
     */
    @Query(
        "SELECT MAX(systolic) AS avgSystolic, MAX(diastolic) AS avgDiastolic, MAX(pulse) AS avgPulse " + "FROM blood_pressure_data_surface " + "WHERE userId = :userId"
    )
    fun getMaxValuesForUser(userId: String): BpBloodPressureAverageValues

    /**
     * 查询指定用户 ID 下的所有 systolic、diastolic 和 pulse 三个字段的最小值
     */
    @Query(
        "SELECT MIN(systolic) AS avgSystolic, MIN(diastolic) AS avgDiastolic, MIN(pulse) AS avgPulse " + "FROM blood_pressure_data_surface " + "WHERE userId = :userId"
    )
    fun getMinValuesForUser(userId: String): BpBloodPressureAverageValues

    /**
     * 获取所有statusType
     */
    @Query("SELECT stateType FROM blood_pressure_data_surface WHERE userId = :userId")
    fun getStateTypesByUserId(userId: String): List<Int>

    /**
     * 查询距离当前最近的3条数据
     */
    @Query("SELECT * FROM blood_pressure_data_surface WHERE userId = :userId ORDER BY timePeriod DESC LIMIT 3")
    fun getLastThreeBloodPressureData(userId: String): List<BloodPressureData>

    @Transaction
    @Query("SELECT * FROM blood_pressure_surface WHERE userId = :userId ORDER BY date DESC LIMIT 3")
    fun getLastThreeBloodPressureWithDetails(userId: String): List<BpBloodPressureWithDetails>
}